package com.shlh.saas.service;

import com.shlh.saas.service.company.CompanyService;
import com.shlh.saas.service.linkedin.LinkedInUserService;
import jakarta.annotation.Resource;
import org.apache.shardingsphere.infra.hint.HintManager;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import javax.sql.DataSource;
import java.util.*;

/**
 * 域名反向查询服务
 * 支持查询所有包含域名、网站、网址等字段的数据表
 */
@Service
public class DomainLookupService {

    // 默认JdbcTemplate，用于所有查询
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    // 注入LinkedIn专用JdbcTemplate
    @Autowired
    private DataSource dataSource;

    @Resource
    private LinkedInUserService linkedInUserService;

    @Resource
    private CompanyService companyService;
    
    // 创建一个专门用于查询LinkedIn数据的方法
    private JdbcTemplate getLinkedInJdbcTemplate() {
        try {
            // 创建一个新的JdbcTemplate，使用独立的连接避免ShardingSphere路由问题
            JdbcTemplate lyDbJdbcTemplate = new JdbcTemplate(dataSource);
            // 设置直接连接到ly_db数据库的查询前缀
            lyDbJdbcTemplate.execute("USE ly_db");
            return lyDbJdbcTemplate;
        } catch (Exception e) {
            System.err.println("创建LinkedIn专用JdbcTemplate失败: " + e.getMessage());
            return jdbcTemplate; // 如果失败则使用默认JdbcTemplate
        }
    }
    
    /**
     * 根据域名进行反向查询
     * @param domain 域名
     * @param page 页码
     * @param size 每页记录数
     * @return 查询结果和统计信息
     */
    public Map<String, Object> lookupByDomain(String domain, int page, int size) {
        // 计算分页参数
        int offset = (page - 1) * size;

        // 预处理域名，确保格式一致
        String searchDomain = preprocessDomain(domain);
        
        // 存储所有查询结果
        List<Map<String, Object>> allResults = new ArrayList<>();
        long total = 0;
        List<String> errors = new ArrayList<>();
        
        try {
            System.out.println("开始查询所有数据库中的域名：" + searchDomain);
            
            // 1. 搜索广交会展商表
            try {
                System.out.println("开始查询广交会展商数据...");
                List<Map<String, Object>> exhibitorsResults = searchCantonFairExhibitors(searchDomain, size, offset);
                if (!exhibitorsResults.isEmpty()) {
                    System.out.println("广交会展商数据找到匹配记录: " + exhibitorsResults.size() + " 条");
                    allResults.addAll(exhibitorsResults);
                    total += countCantonFairExhibitors(searchDomain);
                } else {
                    System.out.println("广交会展商数据未找到匹配记录");
                }
            } catch (Exception e) {
                System.err.println("广交会展商数据查询失败: " + e.getMessage());
                errors.add("广交会展商数据查询失败: " + e.getMessage());
            }
            
            // 2. 搜索广交会采购商表
            try {
                System.out.println("开始查询广交会采购商数据...");
                List<Map<String, Object>> buyersResults = searchCantonFairBuyers(searchDomain, size, offset);
                if (!buyersResults.isEmpty()) {
                    System.out.println("广交会采购商数据找到匹配记录: " + buyersResults.size() + " 条");
                    allResults.addAll(buyersResults);
                    total += countCantonFairBuyers(searchDomain);
                } else {
                    System.out.println("广交会采购商数据未找到匹配记录");
                }
            } catch (Exception e) {
                System.err.println("广交会采购商数据查询失败: " + e.getMessage());
                errors.add("广交会采购商数据查询失败: " + e.getMessage());
            }
            
            // 4. 搜索全球企业库
            try {
                System.out.println("开始查询企业库数据...");
                List<Map<String, Object>> enterpriseResults = searchEnterprise(searchDomain, size, offset);
                if (!enterpriseResults.isEmpty()) {
                    System.out.println("企业库数据找到匹配记录: " + enterpriseResults.size() + " 条");
                    allResults.addAll(enterpriseResults);
                    total += countEnterprise(searchDomain);
                } else {
                    System.out.println("企业库数据未找到匹配记录");
                }
            } catch (Exception e) {
                System.err.println("企业库数据查询失败: " + e.getMessage());
                errors.add("企业库数据查询失败: " + e.getMessage());
            }
            
            // 5. 搜索LinkedIn数据
            try {
                System.out.println("开始查询LinkedIn数据...");
                List<Map<String, Object>> linkedinResults = searchLinkedIn(searchDomain, size, offset);
                if (!linkedinResults.isEmpty()) {
                    System.out.println("LinkedIn数据找到匹配记录: " + linkedinResults.size() + " 条");
                    allResults.addAll(linkedinResults);
                    total += countLinkedIn(searchDomain);
                } else {
                    System.out.println("LinkedIn数据未找到匹配记录");
                }
            } catch (Exception e) {
                System.err.println("LinkedIn数据查询失败: " + e.getMessage());
                errors.add("LinkedIn数据查询失败: " + e.getMessage());
            }
            

        } catch (Exception e) {
            System.err.println("查询过程中发生错误: " + e.getMessage());
            e.printStackTrace();
            errors.add("查询过程中发生错误: " + e.getMessage());
        }

        // 返回结果
        Map<String, Object> response = new HashMap<>();
        response.put("results", allResults);
        response.put("total", total);
        response.put("page", page);
        response.put("size", size);
        
        // 如果有错误，添加到响应中
        if (!errors.isEmpty()) {
            response.put("errors", errors);
            // 只返回第一个错误作为主要错误信息
            response.put("error", errors.get(0));
        }
        
        return response;
    }
    
    /**
     * 预处理域名，确保格式一致
     * 移除http://, https://, www.前缀和尾部的/等
     */
    private String preprocessDomain(String domain) {
        if (domain == null || domain.isEmpty()) {
            return "";
        }
        
        String result = domain.trim().toLowerCase();
        
        // 移除协议前缀
        if (result.startsWith("http://")) {
            result = result.substring(7);
        } else if (result.startsWith("https://")) {
            result = result.substring(8);
        }
        
        // 移除www.前缀
        if (result.startsWith("www.")) {
            result = result.substring(4);
        }
        
        // 移除尾部的/和所有查询参数
        int slashIndex = result.indexOf('/');
        if (slashIndex > 0) {
            result = result.substring(0, slashIndex);
        }
        
        // 移除端口号
        int portIndex = result.indexOf(':');
        if (portIndex > 0) {
            result = result.substring(0, portIndex);
        }
        
        return result;
    }

    /**
     * 查询海关数据
     */
    private List<Map<String, Object>> searchCustoms(String domain, int size, int offset) {
        final List<Map<String, Object>> allResults = new ArrayList<>();
        
        try {
            final int totalShards = 200;
            System.out.println("查询海关数据，分片范围: 0-" + (totalShards-1));
            
            // 根据ShardingSphere配置，正确的表名是customs_trade_{0..199}
            final String tableNamePattern = "customs_trade_%d";
            
            // 确定域名字段
            String[] possibleDomainFields = {
                "website", "web_address", "domain", "url", "web_url", "website_url",
                "company_website", "shipper_website", "consignee_website"
            };
            String validDomainField = "";
            
            // 尝试找到有效的域名字段，使用海关数据专用数据源
            for (String field : possibleDomainFields) {
                try {
                    final String tableName = String.format(tableNamePattern, 0);
                    final String testSql = "SELECT " + field + " FROM " + tableName + " LIMIT 1";
                    jdbcTemplate.queryForObject(testSql, String.class);
                    validDomainField = field;
                    System.out.println("找到有效的海关数据域名字段: " + field);
                    break;
                } catch (Exception e) {
                    // 这个字段不存在，继续尝试下一个
                    System.out.println("无效的海关数据域名字段: " + field);
                }
            }
            
            if (validDomainField.isEmpty()) {
                System.out.println("未找到有效的海关数据域名字段，跳过海关数据查询");
                return allResults;
            }
            
            // 使用正确的表名模式和域名字段查询数据，使用海关数据专用数据源
            for (int i = 0; i < totalShards; i++) {
                final String tableName = String.format(tableNamePattern, i);
                final String sql = "SELECT * FROM " + tableName + " WHERE " + validDomainField + " LIKE ? LIMIT ? OFFSET ?";
                
                try {
                    List<Map<String, Object>> results = jdbcTemplate.queryForList(sql, "%" + domain + "%", size, offset);
                    if (!results.isEmpty()) {
                        final String source = "customs_trade";
                        results.forEach(map -> map.put("source", source));
                        allResults.addAll(results);
                        
                        // 如果结果数量已经足够，可以提前退出
                        if (allResults.size() >= size) {
                            break;
                        }
                    }
                } catch (Exception e) {
                    // 单个分片查询失败，继续下一个
                    System.out.println("海关数据分片 " + i + " 查询失败: " + e.getMessage());
                }
            }
            
            return allResults;
            
        } catch (Exception e) {
            System.err.println("海关数据查询失败: " + e.getMessage());
            return new ArrayList<>();
        }
    }
    
    /**
     * 计算海关数据数量
     */
    private int countCustoms(String domain) {
        int totalCount = 0;
        
        try {
            final int totalShards = 200;
            
            // 根据ShardingSphere配置，正确的表名是customs_trade_{0..199}
            final String tableNamePattern = "customs_trade_%d";
            
            // 确定域名字段
            String[] possibleDomainFields = {
                "website", "web_address", "domain", "url", "web_url", "website_url",
                "company_website", "shipper_website", "consignee_website"
            };
            String validDomainField = "";
            
            // 尝试找到有效的域名字段，使用海关数据专用数据源
            for (String field : possibleDomainFields) {
                try {
                    final String tableName = String.format(tableNamePattern, 0);
                    final String testSql = "SELECT " + field + " FROM " + tableName + " LIMIT 1";
                    jdbcTemplate.queryForObject(testSql, String.class);
                    validDomainField = field;
                    break;
                } catch (Exception e) {
                    // 这个字段不存在，继续尝试下一个
                }
            }
            
            if (validDomainField.isEmpty()) {
                return 0;
            }
            
            // 使用正确的表名模式和域名字段查询数据，使用海关数据专用数据源
            for (int i = 0; i < totalShards; i++) {
                final String tableName = String.format(tableNamePattern, i);
                final String sql = "SELECT COUNT(*) FROM " + tableName + " WHERE " + validDomainField + " LIKE ?";
                
                try {
                    Integer count = jdbcTemplate.queryForObject(sql, Integer.class, "%" + domain + "%");
                    totalCount += (count != null ? count : 0);
                } catch (Exception e) {
                    // 单个分片查询失败，继续下一个
                    System.out.println("海关数据分片 " + i + " 计数查询失败: " + e.getMessage());
                }
            }
            
            return totalCount;
            
        } catch (Exception e) {
            System.err.println("海关数据计数查询失败: " + e.getMessage());
            return 0;
        }
    }

    /**
     * 查询广交会采购商数据
     */
    private List<Map<String, Object>> searchCantonFairBuyers(String domain, int size, int offset) {
        List<Map<String, Object>> allResults = new ArrayList<>();
        
        try {
            // 遍历不同届数的广交会采购商表，根据ShardingSphere配置
            final int[] sessions = {125, 126, 127, 129, 130, 131, 132, 133, 134, 135, 136, 137};
            
            for (int session : sessions) {
                try {
                    String tableName = "canton_fair_buyers.canton_fair_buyers_" + session;
                    final String domainField = "website";
                    
                    // 直接查询，不预先检查表是否存在，避免触发ShardingSphere的PostgreSQL异常
                    try {
                        String sql = "SELECT * FROM " + tableName + " WHERE LOWER(" + domainField + ") LIKE LOWER(?)  LIMIT ? OFFSET ?";
                        List<Map<String, Object>> results = jdbcTemplate.queryForList(sql, "%" + domain + "%", size, offset);
                        
                        if (!results.isEmpty()) {
                            final String source = "canton_fair_buyers";
                            final int sessionNumber = session;
                            results.forEach(map -> {
                                map.put("source", source);
                                map.put("session", sessionNumber);
                            });
                            allResults.addAll(results);
                            
                            // 如果结果数量已经足够，可以提前退出
                            if (allResults.size() >= size) {
                                break;
                            }
                        }
                    } catch (Exception e) {
                        // 表不存在或查询失败，静默跳过
                    }
                } catch (Exception e) {
                    // 静默跳过错误
                }
            }
            
            return allResults;
        } catch (Exception e) {
            System.err.println("广交会采购商数据查询失败: " + e.getMessage());
            return new ArrayList<>();
        }
    }
    
    /**
     * 计算广交会采购商数据数量
     */
    private int countCantonFairBuyers(String domain) {
        int totalCount = 0;
        
        try {
            // 遍历不同届数的广交会采购商表
            final int[] sessions = {125, 126, 127, 129, 130, 131, 132, 133, 134, 135, 136, 137};
            
            for (int session : sessions) {
                try {
                    String tableName = "canton_fair_buyers.canton_fair_buyers_" + session;
                    final String domainField = "website";
                    
                    // 直接查询，不预先检查表是否存在，避免触发ShardingSphere的PostgreSQL异常
                    // 使用LOWER()函数进行不区分大小写的匹配
                    try {
                        String sql = "SELECT COUNT(*) FROM " + tableName + " WHERE LOWER(" + domainField + ") LIKE LOWER(?)";
                        Integer count = jdbcTemplate.queryForObject(sql, Integer.class, "%" + domain + "%");
                        totalCount += (count != null ? count : 0);
                    } catch (Exception e) {
                        // 表不存在或查询失败，静默跳过
                    }
                } catch (Exception e) {
                    // 静默跳过错误
                }
            }
            
            return totalCount;
        } catch (Exception e) {
            System.err.println("广交会采购商数据计数查询失败: " + e.getMessage());
            return 0;
        }
    }
    
    /**
     * 查询广交会展商数据
     */
    private List<Map<String, Object>> searchCantonFairExhibitors(String domain, int size, int offset) {
        List<Map<String, Object>> allResults = new ArrayList<>();
        
        try {
            // 遍历不同届数的广交会展商表
            final int[] sessions = {125, 126, 127, 128, 129, 130, 134, 135, 136, 137};
            
            for (int session : sessions) {
                try {
                    String tableName = "canton_fair_exhibitors.canton_fair_exhibitors_" + session;
                    final String domainField = "website";
                    
                    // 直接查询，不预先检查表是否存在，避免触发ShardingSphere的PostgreSQL异常
                    // 使用LOWER()函数进行不区分大小写的匹配
                    try {
                        String sql = "SELECT * FROM " + tableName + " WHERE LOWER(" + domainField + ") LIKE LOWER(?) LIMIT ? OFFSET ?";
                        List<Map<String, Object>> results = jdbcTemplate.queryForList(sql, "%" + domain + "%", size, offset);
                        
                        if (!results.isEmpty()) {
                            final String source = "canton_fair_exhibitors";
                            final int sessionNumber = session;
                            results.forEach(map -> {
                                map.put("source", source);
                                map.put("session", sessionNumber);
                            });
                            allResults.addAll(results);
                            
                            // 如果结果数量已经足够，可以提前退出
                            if (allResults.size() >= size) {
                                break;
                            }
                        }
                    } catch (Exception e) {
                        // 表不存在或查询失败，静默跳过
                    }
                } catch (Exception e) {
                    // 静默跳过错误
                }
            }
            
            return allResults;
        } catch (Exception e) {
            System.err.println("广交会展商数据查询失败: " + e.getMessage());
            return new ArrayList<>();
        }
    }
    
    /**
     * 计算广交会展商数据数量
     */
    private int countCantonFairExhibitors(String domain) {
        int totalCount = 0;
        
        try {
            // 遍历不同届数的广交会展商表
            final int[] sessions = {125, 126, 127, 128, 129, 130, 134, 135, 136, 137};
            final String domainField = "website";
            
            for (int session : sessions) {
                try {
                    String tableName = "canton_fair_exhibitors.canton_fair_exhibitors_" + session;
                    
                    // 直接查询，不预先检查表是否存在，避免触发ShardingSphere的PostgreSQL异常
                    // 使用LOWER()函数进行不区分大小写的匹配
                    try {
                        String sql = "SELECT COUNT(*) FROM " + tableName + " WHERE LOWER(" + domainField + ") LIKE LOWER(?)";
                        Integer count = jdbcTemplate.queryForObject(sql, Integer.class, "%" + domain + "%");
                        totalCount += (count != null ? count : 0);
                    } catch (Exception e) {
                        // 表不存在或查询失败，静默跳过
                    }
                } catch (Exception e) {
                    // 静默跳过错误
                }
            }
            
            return totalCount;
        } catch (Exception e) {
            System.err.println("广交会展商数据计数查询失败: " + e.getMessage());
            return 0;
        }
    }

    /**
     * 查询Twitter数据
     */
    private List<Map<String, Object>> searchTwitter(String domain, int size, int offset) {
        final List<Map<String, Object>> allResults = new ArrayList<>();
        
        try {
            final int totalShards = 200;
            System.out.println("查询Twitter数据，分片范围: 0-" + (totalShards-1));
            
            // 根据ShardingSphere配置，正确的表名是twitter_user_{0..199}
            final String tableNamePattern = "twitter_user_%d";
            
            // 确定域名字段
            String[] possibleDomainFields = {"website", "url", "web_url", "domain", "web_address", "website_url"};
            String validDomainField = "";
            
            // 尝试找到有效的域名字段，使用Twitter专用数据源
            for (String field : possibleDomainFields) {
                try {
                    final String tableName = String.format(tableNamePattern, 0);
                    final String testSql = "SELECT " + field + " FROM " + tableName + " LIMIT 1";
                    jdbcTemplate.queryForObject(testSql, String.class);
                    validDomainField = field;
                    System.out.println("找到有效的Twitter域名字段: " + field);
                    break;
                } catch (Exception e) {
                    // 这个字段不存在，继续尝试下一个
                    System.out.println("无效的Twitter域名字段: " + field);
                }
            }
            
            if (validDomainField.isEmpty()) {
                System.out.println("未找到有效的Twitter域名字段，跳过Twitter数据查询");
                return allResults;
            }
            
            // 使用正确的表名模式和域名字段查询数据，使用Twitter专用数据源
            for (int i = 0; i < totalShards; i++) {
                final String tableName = String.format(tableNamePattern, i);
                final String sql = "SELECT * FROM " + tableName + " WHERE " + validDomainField + " LIKE ? LIMIT ? OFFSET ?";
                
                try {
                    List<Map<String, Object>> results = jdbcTemplate.queryForList(sql, "%" + domain + "%", size, offset);
                    if (!results.isEmpty()) {
                        final String source = "twitter_data";
                        results.forEach(map -> map.put("source", source));
                        allResults.addAll(results);
                        
                        // 如果结果数量已经足够，可以提前退出
                        if (allResults.size() >= size) {
                            break;
                        }
                    }
                } catch (Exception e) {
                    // 单个分片查询失败，继续下一个
                    System.out.println("Twitter分片 " + i + " 查询失败: " + e.getMessage());
                }
            }
            
            return allResults;
            
        } catch (Exception e) {
            System.err.println("Twitter数据查询失败: " + e.getMessage());
            return new ArrayList<>();
        }
    }
    
    /**
     * 计算Twitter数据数量
     */
    private int countTwitter(String domain) {
        int totalCount = 0;
        
        try {
            final int totalShards = 200;
            
            // 根据ShardingSphere配置，正确的表名是twitter_user_{0..199}
            final String tableNamePattern = "twitter_user_%d";
            
            // 确定域名字段
            String[] possibleDomainFields = {"website", "url", "web_url", "domain", "web_address", "website_url"};
            String validDomainField = "";
            
            // 尝试找到有效的域名字段，使用Twitter专用数据源
            for (String field : possibleDomainFields) {
                try {
                    final String tableName = String.format(tableNamePattern, 0);
                    final String testSql = "SELECT " + field + " FROM " + tableName + " LIMIT 1";
                    jdbcTemplate.queryForObject(testSql, String.class);
                    validDomainField = field;
                    break;
                } catch (Exception e) {
                    // 这个字段不存在，继续尝试下一个
                }
            }
            
            if (validDomainField.isEmpty()) {
                return 0;
            }
            
            // 使用正确的表名模式和域名字段查询数据，使用Twitter专用数据源
            for (int i = 0; i < totalShards; i++) {
                final String tableName = String.format(tableNamePattern, i);
                final String sql = "SELECT COUNT(*) FROM " + tableName + " WHERE " + validDomainField + " LIKE ?";
                
                try {
                    Integer count = jdbcTemplate.queryForObject(sql, Integer.class, "%" + domain + "%");
                    totalCount += (count != null ? count : 0);
                } catch (Exception e) {
                    // 单个分片查询失败，继续下一个
                    System.out.println("Twitter分片 " + i + " 计数查询失败: " + e.getMessage());
                }
            }
            
            return totalCount;
            
        } catch (Exception e) {
            System.err.println("Twitter数据计数查询失败: " + e.getMessage());
            return 0;
        }
    }

    /**
     * 查询Facebook数据
     */
    private List<Map<String, Object>> searchFacebook(String domain, int size, int offset) {
        final List<Map<String, Object>> allResults = new ArrayList<>();
        
        try {
            final int totalShards = 200; // 完整查询facebook分片表
            System.out.println("查询Facebook数据，分片范围: 0-" + (totalShards-1));
            
            // 根据ShardingSphere配置，正确的表名是facebook_users_{0..199}
            final String tableNamePattern = "facebook_users_%d";
            
            // 确定域名字段
            String[] possibleDomainFields = {"website", "web_address", "domain", "url", "web_url", "website_url"};
            String validDomainField = "";
            
            // 尝试找到有效的域名字段，使用Facebook专用数据源
            for (String field : possibleDomainFields) {
                try {
                    final String tableName = String.format(tableNamePattern, 0);
                    final String testSql = "SELECT " + field + " FROM " + tableName + " LIMIT 1";
                    jdbcTemplate.queryForObject(testSql, String.class);
                    // 如果没有抛出异常，说明字段存在
                    validDomainField = field;
                    System.out.println("找到有效的Facebook域名字段: " + field);
                    break;
                } catch (Exception e) {
                    // 这个字段不存在，继续尝试下一个
                    System.out.println("无效的Facebook域名字段: " + field);
                }
            }
            
            if (validDomainField.isEmpty()) {
                System.out.println("未找到有效的Facebook域名字段，跳过Facebook数据查询");
                return allResults;
            }
            
            // 使用正确的表名模式和域名字段查询数据，使用Facebook专用数据源
            for (int i = 0; i < totalShards; i++) {
                final String tableName = String.format(tableNamePattern, i);
                final String sql = "SELECT * FROM " + tableName + " WHERE " + validDomainField + " LIKE ? LIMIT ? OFFSET ?";
                
                try {
                    List<Map<String, Object>> results = jdbcTemplate.queryForList(sql, "%" + domain + "%", size, offset);
                    if (!results.isEmpty()) {
                        final String source = "facebook_data";
                        results.forEach(map -> map.put("source", source));
                        allResults.addAll(results);
                        
                        // 如果结果数量已经足够，可以提前退出
                        if (allResults.size() >= size) {
                            break;
                        }
                    }
                } catch (Exception e) {
                    // 单个分片查询失败，继续下一个
                    System.out.println("Facebook分片 " + i + " 查询失败: " + e.getMessage());
                }
            }
            
            return allResults;
            
        } catch (Exception e) {
            System.err.println("Facebook数据查询失败: " + e.getMessage());
            return new ArrayList<>();
        }
    }
    
    /**
     * 计算Facebook数据数量
     */
    private int countFacebook(String domain) {
        int totalCount = 0;
        
        try {
            final int totalShards = 200;
            
            // 根据ShardingSphere配置，正确的表名是facebook_users_{0..199}
            final String tableNamePattern = "facebook_users_%d";
            
            // 确定域名字段
            String[] possibleDomainFields = {"website", "web_address", "domain", "url", "web_url", "website_url"};
            String validDomainField = "";
            
            // 尝试找到有效的域名字段，使用Facebook专用数据源
            for (String field : possibleDomainFields) {
                try {
                    final String tableName = String.format(tableNamePattern, 0);
                    final String testSql = "SELECT " + field + " FROM " + tableName + " LIMIT 1";
                    jdbcTemplate.queryForObject(testSql, String.class);
                    validDomainField = field;
                    break;
                } catch (Exception e) {
                    // 这个字段不存在，继续尝试下一个
                }
            }
            
            if (validDomainField.isEmpty()) {
                return 0;
            }
            
            // 使用正确的表名模式和域名字段查询数据，使用Facebook专用数据源
            for (int i = 0; i < totalShards; i++) {
                final String tableName = String.format(tableNamePattern, i);
                final String sql = "SELECT COUNT(*) FROM " + tableName + " WHERE " + validDomainField + " LIKE ?";
                
                try {
                    Integer count = jdbcTemplate.queryForObject(sql, Integer.class, "%" + domain + "%");
                    totalCount += (count != null ? count : 0);
                } catch (Exception e) {
                    // 单个分片查询失败，继续下一个
                    System.out.println("Facebook分片 " + i + " 计数查询失败: " + e.getMessage());
                }
            }
            
            return totalCount;
            
        } catch (Exception e) {
            System.err.println("Facebook数据计数查询失败: " + e.getMessage());
            return 0;
        }
    }
    
    /**
     * 查询全球企业库数据
     */
    private List<Map<String, Object>> searchEnterprise(String domain, int size, int offset) {
        List<Map<String, Object>> allResults = new ArrayList<>();
        
        try {

            List<Map<String, Object>> results = companyService.findByDomain(domain, size, offset);
            final String source = "global_enterprise_library";
            results.forEach(map -> map.put("source", source));
            allResults.addAll(results);

            return allResults;

        } catch (Exception e) {
            System.err.println("企业库数据查询失败: " + e.getMessage());
            return new ArrayList<>();
        }
    }
    
    /**
     * 计算全球企业库数据数量
     */
    private long countEnterprise(String domain) {
        long totalCount = 0;
        
        try {
            long count = companyService.countByDomain(domain);
            totalCount += count;
            return totalCount;

        } catch (Exception e) {
            System.err.println("企业库数据计数查询失败: " + e.getMessage());
            return 0;
        }
    }

    /**
     * 查询LinkedIn数据
     */
    private List<Map<String, Object>> searchLinkedIn(String domain, int size, int offset) {
        final List<Map<String, Object>> allResults = new ArrayList<>();
        
        try {
            List<Map<String,Object>> results = linkedInUserService.findByDomain(domain, size, offset);
            final String source = "linkedin_data";
            results.forEach(map -> map.put("source", source));
            allResults.addAll(results);

            return allResults;
            
        } catch (Exception e) {
            System.err.println("LinkedIn数据查询失败: " + e.getMessage());
            return new ArrayList<>();
        }
    }
    
    /**
     * 计算LinkedIn数据数量
     */
    private long countLinkedIn(String domain) {
        long totalCount = 0;
        
        try {

            long count = linkedInUserService.countByDomain(domain);
            totalCount += count;
            return totalCount;

//            final int totalShards = 200;
//
//            // 创建LinkedIn专用JdbcTemplate
//            JdbcTemplate lyDbJdbcTemplate = getLinkedInJdbcTemplate();
//
//            // 根据ShardingSphere配置，正确的表名是ly_user_{0..199}
//            final String tableNamePattern = "ly_user_%d";
//
//            // 确定域名字段 - 根据提示，LinkedIn表的域名字段是company_website
//            String[] possibleDomainFields = {
//                "company_website", // LinkedIn表的实际域名字段，优先检查
//                "Company_Website",
//                "website",
//                "web_address",
//                "domain",
//                "url",
//                "web_url",
//                "website_url",
//                "company_domain"
//            };
//            String validDomainField = "";
//
//            // 尝试找到有效的域名字段
//            for (String field : possibleDomainFields) {
//                try {
//                    final String tableName = String.format(tableNamePattern, 0);
//                    final String testSql = "SELECT " + field + " FROM " + tableName + " LIMIT 1";
//
//                    lyDbJdbcTemplate.queryForObject(testSql, String.class);
//                    validDomainField = field;
//                    break;
//                } catch (Exception e) {
//                    // 这个字段不存在，继续尝试下一个
//                }
//            }
//
//            if (validDomainField.isEmpty()) {
//                return 0;
//            }
//
//            // 使用正确的表名模式和域名字段查询数据
//            for (int i = 0; i < totalShards; i++) {
//                final String tableName = String.format(tableNamePattern, i);
//                final String sql = "SELECT COUNT(*) FROM " + tableName + " WHERE " + validDomainField + " LIKE ?";
//
//                try {
//                    Integer count = lyDbJdbcTemplate.queryForObject(sql, Integer.class, "%" + domain + "%");
//                    totalCount += (count != null ? count : 0);
//                } catch (Exception e) {
//                    // 单个分片查询失败，继续下一个
//                    System.out.println("LinkedIn分片 " + i + " 计数查询失败: " + e.getMessage());
//                }
//            }
//
//            return totalCount;
            
        } catch (Exception e) {
            System.err.println("LinkedIn数据计数查询失败: " + e.getMessage());
            return 0;
        }
    }

    /**
     * 批量域名反查 - 查询LinkedIn数据
     */
    public List<Map<String, Object>> searchLinkedInByDomain(String domain) {
        try {
            String searchDomain = preprocessDomain(domain);
            List<Map<String, Object>> results = searchLinkedIn(searchDomain, 100, 0);
            // 转换字段名以匹配Excel表头
            return results.stream().map(this::convertLinkedInMapForExcel).toList();
        } catch (Exception e) {
            e.printStackTrace();
            return new ArrayList<>();
        }
    }

    /**
     * 批量域名反查 - 查询全球企业库数据
     */
    public List<Map<String, Object>> searchCompanyByDomain(String domain) {
        try {
            String searchDomain = preprocessDomain(domain);
            List<Map<String, Object>> results = searchEnterprise(searchDomain, 100, 0);
            // 转换字段名以匹配Excel表头
            return results.stream().map(this::convertCompanyMapForExcel).toList();
        } catch (Exception e) {
            e.printStackTrace();
            return new ArrayList<>();
        }
    }

    /**
     * 批量域名反查 - 查询广交会采购商数据
     */
    public List<Map<String, Object>> searchBuyerByDomain(String domain) {
        try {
            String searchDomain = preprocessDomain(domain);
            List<Map<String, Object>> results = searchCantonFairBuyers(searchDomain, 100, 0);
            // 转换字段名以匹配Excel表头
            return results.stream().map(this::convertBuyerMapForExcel).toList();
        } catch (Exception e) {
            e.printStackTrace();
            return new ArrayList<>();
        }
    }

    /**
     * 批量域名反查 - 查询广交会展商数据
     */
    public List<Map<String, Object>> searchExhibitorByDomain(String domain) {
        try {
            String searchDomain = preprocessDomain(domain);
            List<Map<String, Object>> results = searchCantonFairExhibitors(searchDomain, 100, 0);
            // 转换字段名以匹配Excel表头
            return results.stream().map(this::convertExhibitorMapForExcel).toList();
        } catch (Exception e) {
            e.printStackTrace();
            return new ArrayList<>();
        }
    }

    // 数据转换方法
    private Map<String, Object> convertLinkedInMapForExcel(Map<String, Object> source) {
        Map<String, Object> result = new HashMap<>();
        result.put("公司名", source.get("company_name"));
        result.put("人名", source.get("full_name"));
        result.put("网址", source.get("company_website"));
        result.put("职位", source.get("job_title"));
        result.put("行业", source.get("industry"));
        result.put("邮箱", source.get("emails"));
        result.put("电话", source.get("phone_numbers"));
        result.put("手机号", source.get("mobile"));
        result.put("位置", source.get("location"));
        result.put("地点", source.get("street_address"));
        result.put("国家", source.get("countries"));
        result.put("公司规模", source.get("company_size"));
        result.put("twitter用户", source.get("twitter_user_name"));
        result.put("twitter链接", source.get("twitter_url"));
        result.put("LinkedIn链接", source.get("linkedin_url"));
        result.put("GitHub链接", source.get("github_url"));
        result.put("Facebook链接", source.get("facebook_url"));
        return result;
    }

    private Map<String, Object> convertCompanyMapForExcel(Map<String, Object> source) {
        Map<String, Object> result = new HashMap<>();
        
        // 组合姓名
        String fullName = "";
        Object firstName = source.get("FIRST_NAME");
        Object lastName = source.get("LAST_NAME");
        
        if (firstName != null && !firstName.toString().isEmpty()) {
            fullName += firstName;
        }

        if (lastName != null && !lastName.toString().isEmpty()) {
            if (!fullName.isEmpty()) fullName += " ";
            fullName += lastName;
        }
        
        result.put("公司名", source.get("COMPANY_NAME"));
        result.put("姓名", fullName);
        result.put("职位", source.get("JOB_TITLE"));
        result.put("省份", source.get("COMPANY_STATE"));
        result.put("行业", source.get("COMPANY_INDUSTRY"));
        result.put("子行业", source.get("PRIMARY_INDUSTRY"));
        result.put("员工规模", source.get("COMPANY_EMPLOYEE_COUNT"));
        result.put("收入规模", source.get("COMPANY_REVENUE"));
        result.put("电话", source.get("COMPANY_PHONE"));
        result.put("手机号", source.get("MOBILE_PHONE"));
        result.put("网址", source.get("COMPANY_DOMAIN"));
        result.put("邮箱", source.get("BUSINESS_EMAIL"));
        result.put("地址", source.get("COMPANY_ADDRESS"));
        result.put("邮编", source.get("COMPANY_ZIP"));
        return result;
    }

    private Map<String, Object> convertBuyerMapForExcel(Map<String, Object> source) {
        Map<String, Object> result = new HashMap<>();
        result.put("公司名", source.get("company_name"));
        result.put("联系人", source.get("contact_person"));
        result.put("国家/地区", source.get("country"));
        if (source.get("demand_goods") != null && !source.get("demand_goods").toString().isEmpty()) {
            result.put("采购类型", source.get("demand_goods"));
        } else {
            result.put("采购类型", source.get("product_category"));
        }
        result.put("网址", source.get("website"));
        result.put("邮箱", source.get("email"));
        result.put("电话", source.get("telephone"));
        result.put("传真", source.get("fax"));
        result.put("地址", source.get("address"));
        result.put("邮编", source.get("zip_code"));
        result.put("广交会届数", source.get("session"));
        return result;
    }

    private Map<String, Object> convertExhibitorMapForExcel(Map<String, Object> source) {
        Map<String, Object> result = new HashMap<>();
        result.put("公司名", source.get("company_name"));
        result.put("公司类型", source.get("company_type"));
        result.put("公司简介", source.get("company_profile"));
        result.put("联系人", source.get("contact_person"));
        result.put("手机号", source.get("mobile"));
        result.put("固定电话", source.get("phone"));
        result.put("网址", source.get("website"));
        result.put("地址", source.get("contact_address"));
        result.put("邮箱", source.get("email"));
        result.put("传真", source.get("fax"));
        result.put("邮编", source.get("postal_code"));
        result.put("行业", source.get("category"));
        result.put("省份", source.get("province_city"));
        result.put("展位号", source.get("booth_number"));
        result.put("主要产品", source.get("main_products"));
        result.put("公司规模", source.get("company_scale"));
        result.put("成立年份", source.get("establishment_year"));
        result.put("注册资金", source.get("registered_capital"));
        result.put("历史参展", source.get("recent_exhibition_history"));
        result.put("届次", source.get("session"));
        return result;
    }
} 